﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Text;
using Newtonsoft.Json;

namespace KillmailGrabberExport
{
    class Program
    {
        private const string SELECT_KILLS = "select K.[killInternalID],K.[killTime],K.[solarSystemID] from Kills K";
        private const string SELECT_VICTIM = "select V.[characterID],V.[characterName],V.[corporationID],V.[corporationName],V.[allianceID],V.[allianceName],V.[factionID],V.[factionName],V.[shipTypeID],V.[damageTaken] from victims V where V.[killInternalID] = ?";
        private const string SELECT_INVOLVED = "select A.[characterID],A.[characterName],A.[corporationID],A.[corporationName],A.[factionID],A.[factionName],A.[allianceID],A.[allianceName],A.[finalBlow],A.[damageDone],A.[securityStatus],A.[shipTypeID],A.[weaponTypeID] from Attackers A where A.[killInternalID] = ?";
        private const string SELECT_ITEMS = "select I.[typeID],I.[qtyDestroyed],I.[qtyDropped],I.[flag] from Items I where I.[killInternalID]=?";

        static void Main(string[] args)
        {
            var kills = new Queue<Killmail>();
            var ser = new JsonSerializer();
            File.Delete(@"c:\users\etil\desktop\kills.json");

            using (var outStream = new StreamWriter(@"c:\users\etil\desktop\kills.json", true, Encoding.UTF8))
            using (var writer = new JsonTextWriter(outStream){ Formatting  =  Formatting.None, CloseOutput = true })
            using (var cnn = new SQLiteConnection(@"data source=C:\Users\etil\Desktop\cache\www.eve-kill.net\plt_Etil DeLaFuente\cacheFile.db"))
            using (var cmd = cnn.CreateCommand())
            {
                cnn.Open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SELECT_KILLS;
                using(var killsreader = cmd.ExecuteReader())
                    while(killsreader.Read())
                    {
                        kills.Enqueue(new Killmail()
                                       {
                                           KillInternalId = killsreader.GetInt32(0),
                                           KillDate = killsreader.GetDateTime(1),
                                           KillSolarSystemId = killsreader.GetInt32(2)
                                       });
                    }
                kills.TrimExcess();

                cmd.Parameters.Add("internalId", DbType.Int32);

                writer.WriteStartArray();

                int tot = kills.Count;
                while (tot > 0)
                {
                    var killmail = kills.Dequeue();
                    tot--;

                    Console.Clear();
                    Console.WriteLine("Count : {0}", tot);

                    cmd.Parameters[0].Value = killmail.KillInternalId;
                    
                    //Console.WriteLine("KillInternalId = {0}", killmail.KillInternalId);

                    cmd.CommandText = SELECT_VICTIM;
                    using (var reader = cmd.ExecuteReader())
                    {
                        reader.Read();
                        killmail.Victim = new Victim()
                        {
                            VictimCharacterId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0),
                            VictimCharacterName = reader.GetString(1).Replace("#", "'"),
                            VictimCorporationId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2),
                            VictimCorporationName = reader.GetString(3).Replace("#", "'"),
                            VictimFactionId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                            VictimFactionName = reader.GetString(5),
                            VictimAllianceId = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
                            VictimAllianceName = reader.GetString(7).Replace("#", "'"),
                            VictimDestroyedTypeId = reader.IsDBNull(8) ? 0 : reader.GetInt32(8),
                            VictimDamageTaken = reader.IsDBNull(9) ? 0 : reader.GetInt32(9)
                        };
                    }

                    cmd.CommandText = SELECT_INVOLVED;
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            killmail.Involved = new List<Involved>();
                            while (reader.Read())
                            {
                                killmail.Involved.Add(new Involved()
                                {
                                    AttackerCharacterId = reader.IsDBNull(0) ? 0 : reader.GetInt64(0),
                                    AttackerCharacterName = reader.GetString(1).Replace("#", "'"),
                                    AttackerCorporationId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2),
                                    AttackerCorporationName = reader.GetString(3).Replace("#", "'"),
                                    AttackerFactionId = reader.IsDBNull(4) ? 0 : reader.GetInt32(4),
                                    AttackerFactionName = reader.GetString(5),
                                    AttackerAllianceId = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
                                    AttackerAllianceName = reader.GetString(7).Replace("#", "'"),
                                    AttackerFinalBlow = reader.IsDBNull(8) ? false : reader.GetBoolean(8),
                                    AttackerDamageDone = reader.IsDBNull(9) ? 0 : reader.GetInt32(9),
                                    AttackerSecurityStatus = reader.IsDBNull(10) ? 0 : reader.GetFloat(10),
                                    AttackerShipTypeId = reader.IsDBNull(11) ? 0 : reader.GetInt32(11),
                                    AttackerWeaponTypeId = reader.IsDBNull(12) ? 0 : reader.GetInt32(12)
                                });
                            }
                        }
                        
                    }

                    cmd.CommandText = SELECT_ITEMS;
                    using (var reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            killmail.Items = new List<Item>();
                            while (reader.Read())
                            {
                                killmail.Items.Add(new Item()
                                {
                                    ItemTypeId = reader.IsDBNull(0) ? 0 : reader.GetInt32(0),
                                    ItemQtyDestroyed = reader.IsDBNull(1) ? 0 : reader.GetInt32(1),
                                    ItemQtyDropped = reader.IsDBNull(2) ? 0 : reader.GetInt32(2),
                                    ItemFlag = reader.IsDBNull(3) ? 0 : reader.GetInt32(3)
                                });
                            }
                        }
                            
                    }
                    ser.Serialize(writer, killmail);
                }
                writer.WriteEndArray();
                writer.Flush();
                writer.Close();
            }
        }
    }
    [JsonObject(MemberSerialization.OptIn)]
    class Killmail
    {
        [JsonProperty("internalId")]
        public long KillInternalId { get; set; }
        [JsonProperty("date")]
        public DateTime KillDate { get; set; }
        [JsonProperty("systemId")]
        public long KillSolarSystemId { get; set; }
        [JsonProperty("victim")]
        public Victim Victim { get; set; }
        [JsonProperty("involved")]
        public List<Involved> Involved { get; set; }
        [JsonProperty("items")]
        public List<Item> Items { get; set; }
    }
    [JsonObject(MemberSerialization.OptIn)]
    class Victim
    {
        [JsonProperty("id")]
        public long VictimCharacterId { get; set; }
        [JsonProperty("Name")]
        public string VictimCharacterName { get; set; }
        [JsonProperty("corpId")]
        public long VictimCorporationId { get; set; }
        [JsonProperty("corpName")]
        public string VictimCorporationName { get; set; }
        [JsonProperty("allyId")]
        public long VictimAllianceId { get; set; }
        [JsonProperty("allyName")]
        public string VictimAllianceName { get; set; }
        [JsonProperty("factionId")]
        public int VictimFactionId { get; set; }
        [JsonProperty("factionName")]
        public string VictimFactionName { get; set; }
        [JsonProperty("shipId")]
        public int VictimDestroyedTypeId { get; set; }
        [JsonProperty("damage")]
        public int VictimDamageTaken { get; set; }
    }

    [JsonObject(MemberSerialization.OptIn)]
    class Involved
    {
        [JsonProperty("id")]
        public long AttackerCharacterId { get; set; }
        [JsonProperty("name")]
        public string AttackerCharacterName { get; set; }
        [JsonProperty("corpId")]
        public long AttackerCorporationId { get; set; }
        [JsonProperty("corpName")]
        public string AttackerCorporationName { get; set; }
        [JsonProperty("factionId")]
        public int AttackerFactionId { get; set; }
        [JsonProperty("factionName")]
        public string AttackerFactionName { get; set; }
        [JsonProperty("allyId")]
        public long AttackerAllianceId { get; set; }
        [JsonProperty("allyName")]
        public string AttackerAllianceName { get; set; }
        [JsonProperty("finalBlow")]
        public bool AttackerFinalBlow { get; set; }
        [JsonProperty("damage")]
        public long AttackerDamageDone { get; set; }
        [JsonProperty("security")]
        public float AttackerSecurityStatus { get; set; }
        [JsonProperty("shipId")]
        public int AttackerShipTypeId { get; set; }
        [JsonProperty("weaponId")]
        public int AttackerWeaponTypeId { get; set; }
    }

    [JsonObject(MemberSerialization.OptIn)]
    class Item
    {
        [JsonProperty("itemId")]
        public int ItemTypeId { get; set; }
        [JsonProperty("qtyDestroyed")]
        public int ItemQtyDestroyed { get; set; }
        [JsonProperty("qtyDropped")]
        public int ItemQtyDropped { get; set; }
        [JsonProperty("flag")]
        public int ItemFlag { get; set; }
    }
}
